package com.zking.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;

import com.zking.entitys.ProductInfo;
import com.zking.jdbc.JDBCUtil;

public class ProductInfoDAO {
	public void add(ProductInfo entity){
		String sql="insert into product_info values(?,?,?,?,?,?,?,?,?)";
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			pstmt.setObject(1, entity.getProductId());
			pstmt.setString(2, entity.getProductCategory());
			pstmt.setString(3, entity.getProductBrand());
			pstmt.setString(4, entity.getProductName());
			pstmt.setObject(5, entity.getPurchasingPrice());
			pstmt.setObject(6, entity.getProductPrice());
			pstmt.setObject(7, entity.getDateOfManufacture());
			pstmt.setObject(8, entity.getQualityGuaranteePeriod());
			pstmt.setObject(9, entity.getProductCount());
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	public void update(ProductInfo entity){
		String sql="update product_info set product_category=?,product_brand=?,product_name=?,purchasing_price=?,product_price=?,date_of_manufacture=?,quality_guarantee_period=?,product_count=? where product_id=?";
		try(
				Connection conn=JDBCUtil.getConnection();
				PreparedStatement pstmt=conn.prepareStatement(sql);
			){
			pstmt.setObject(9, entity.getProductId());
			pstmt.setString(1, entity.getProductCategory());
			pstmt.setString(2, entity.getProductBrand());
			pstmt.setString(3, entity.getProductName());
			pstmt.setObject(4, entity.getPurchasingPrice());
			pstmt.setObject(5, entity.getProductPrice());
			pstmt.setObject(6, entity.getDateOfManufacture());
			pstmt.setObject(7, entity.getQualityGuaranteePeriod());
			pstmt.setObject(8, entity.getProductCount());
			pstmt.executeUpdate();
			}catch(Exception e){
				e.printStackTrace();
			}
	}
	public void delete(Serializable id){
		String sql="delete from product_info where product_id=?";
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			pstmt.setObject(1, id);
			pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void deleteCount(Serializable id,Integer count){
		String sql="update product_info set product_count=? where product_id=?";
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			ProductInfo entity=selectById(id);
			pstmt.setObject(1, (entity.getProductCount()-count));
			pstmt.setObject(2, entity.getProductId());
			pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	public ProductInfo selectById(Serializable id){
		String sql="select * from product_info where product_id=?";
		ResultSet rs=null;
		ProductInfo p=null;
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			pstmt.setObject(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				p=load(rs);
			}
		}catch (Exception e) {
			
		}finally{
			JDBCUtil.close(rs);
		}
		return p;
	}
	public List<ProductInfo> select(int index,int len){
		List<ProductInfo> list=new ArrayList<>();
		String sql="select * from product_info order by date_of_manufacture desc limit ?,?";
		ResultSet rs=null;
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			pstmt.setInt(1, index);
			pstmt.setInt(2,len);
			rs=pstmt.executeQuery();
			while(rs.next()){
				list.add(load(rs));
			}
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			JDBCUtil.close(rs);
		}
		return list;
	}
	public long count(){
		String sql="select count(*) c from product_info";
		ResultSet rs=null;
		try(
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pstmt=conn.prepareStatement(sql);
		){
			rs=pstmt.executeQuery();
			rs.next();
			return rs.getLong("c");
		}catch (Exception e) {
			// TODO: handle exception
		}finally{
			JDBCUtil.close(rs);
		}
		return 0;
	}
	public static ProductInfo load(ResultSet rs){
		ProductInfo p=new ProductInfo();
		try {
			p.setProductId(rs.getInt("product_id"));
			p.setProductCategory(rs.getString("product_category"));
			p.setProductBrand(rs.getString("product_brand"));
			p.setProductName(rs.getString("product_name"));
			p.setDateOfManufacture(rs.getDate("date_of_manufacture"));
			if(rs.getObject("purchasing_price")!=null){
				p.setPurchasingPrice(rs.getDouble("purchasing_price"));
			}
			if(rs.getObject("quality_guarantee_period")!=null){
				p.setQualityGuaranteePeriod(rs.getInt("quality_guarantee_period"));
			}
			if(rs.getObject("product_price")!=null){
				p.setProductPrice(rs.getDouble("product_price"));
			}
			if(rs.getObject("product_count")!=null){
				p.setProductCount(rs.getInt("product_count"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
}
